package com.zhonggong.empProducer.dao.impl;

import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import com.zhonggong.empProducer.dao.ProducerDao;
import com.zhonggong.network.emp.model.Emp;

public class ProducerDaoImpl extends Variable implements ProducerDao {

	List<Emp> emps = null;

	public void connection() throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver");
		this.con = DriverManager.getConnection(url, usr, password);
	}

	public void refuse() throws SQLException {
		if (this.rs != null)
			rs.close();
		if (this.ps != null)
			ps.close();
		if (this.st != null)
			st.close();
		if (this.con != null)
			con.close();
	}

	public void add(Emp emp) {
		try {
			connection();
			this.sql = "insert into e_emp(name,gongzi,bumen) values(?,?,?)";
			this.ps = con.prepareStatement(sql);
			ps.setString(1, emp.getName());
			ps.setString(2, emp.getGongzi());
			ps.setString(3, emp.getBumen());
			ps.executeUpdate();
			refuse();
		} catch (ClassNotFoundException e) {
			logger.info("连接失败！");
			e.printStackTrace();
		} catch (SQLException e) {
			logger.info("出现异常！");
			e.printStackTrace();
		}

	}

	public void delete(Emp emp) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			this.con = DriverManager.getConnection(url, usr, password);
			this.sql = "delete from e_emp where name=?";
			this.ps = con.prepareStatement(sql);
			ps.setString(1, emp.getName());
			ps.executeUpdate();
			refuse();
		} catch (ClassNotFoundException e) {
			logger.info("连接失败！");
			e.printStackTrace();
		} catch (SQLException e) {
			logger.info("出现异常！");
			e.printStackTrace();
		}
	}

	public	Emp findByName(String name) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			this.con = DriverManager.getConnection(url, usr, password);
			this.sql = "SELECT NAME,GONGZI,BUMEN FROM E_EMP WHERE Name=?";
			this.ps = con.prepareStatement(sql);
			ps.setString(1, name);
			this.rs = ps.executeQuery();
			if (rs.next()) {
				String Name = rs.getString(1);
				String gongzi = rs.getString(2);
				String bumen = rs.getString(3);
				Emp emp = new Emp(Name, gongzi, bumen);
				return emp;
			}
			refuse();
			return null;
		} catch (ClassNotFoundException e) {
			logger.info("连接失败！");
			e.printStackTrace();
			return null;
		} catch (SQLException e) {
			logger.info("出现异常！");
			e.printStackTrace();
			return null;
		}
	}

	public List<Emp> queryall() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			this.con = DriverManager.getConnection(url, usr, password);
			this.st = con.createStatement();
			this.sql = "SELECT NAME,GONGZI,BUMEN FROM E_EMP";
			this.rs = st.executeQuery(sql);
			emps = new ArrayList<Emp>();
			while (rs.next()) {
				Emp emp = new Emp();
				emp.setName(rs.getString(1));
				emp.setGongzi(rs.getString(2));
				emp.setBumen(rs.getString(3));
				emps.add(emp);
			}
			refuse();
		} catch (ClassNotFoundException e) {
			logger.info("连接失败！");
			e.printStackTrace();
		} catch (SQLException e) {
			logger.info("出现异常！");
			e.printStackTrace();
		}
		return emps;
	}

	public void upDate(Emp emp) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			this.con = DriverManager.getConnection(url, usr, password);
			this.sql = "UPDATE e_emp SET gongzi=?,bumen=? where Name=?";
			this.ps = con.prepareStatement(sql);
			
			ps.setString(1, emp.getGongzi());
			ps.setString(2, emp.getBumen());
			ps.setString(3, emp.getName());
			ps.execute();
			refuse();
		} catch (ClassNotFoundException e) {
			logger.info("连接失败！");
			e.printStackTrace();
		} catch (SQLException e) {
			logger.info("出现异常！");
			e.printStackTrace();
		}
	}
	
	public List<Emp>imPort(String tablename) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			this.con = DriverManager.getConnection(url, usr, password);
			this.st = con.createStatement();
			this.sql = "SELECT NAME,PRICE,SIZE FROM "+tablename;
			this.rs = st.executeQuery(sql);
			emps = new ArrayList<Emp>();
			while (rs.next()) {
				Emp emp = new Emp();
				emp.setName(rs.getString(1));
				emp.setGongzi(rs.getString(2));
				emp.setBumen(rs.getString(3));
				emps.add(emp);
			}
			Iterator<Emp> iterator=emps.iterator();
			while(iterator.hasNext()) {
				Emp emp=iterator.next();
				logger.info("名字："+emp.getName()+"工资："+emp.getGongzi()+"部门："+emp.getBumen());
			}
			refuse();
		} catch (ClassNotFoundException e) {
			logger.info("连接失败！");
			e.printStackTrace();
		} catch (SQLException e) {
			logger.info("出现异常！");
			e.printStackTrace();
		}
		return emps;
	}
}
